﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

using MySql.Data; //needed for mysql connector
using MySql.Data.MySqlClient;

using System.Configuration;

namespace Genealogy_Builder
{
    public partial class GenealogyBuilder : Form
    {
        FileStream FS;

        public static int pid = 0;
        public static int place_id = 0;

        //public List<Person> PeopleList;
        public List<Relation> RelationList;
        public List<HasTrait> HasTraitList;

        public List<Trait> TraitList;
        public List<Place> PlaceList;

        private MySqlConnection conn;

        public GenealogyBuilder()
        {
            InitializeComponent();
            RelationList = new List<Relation>();
            HasTraitList = new List<HasTrait>();
            TraitList = new List<Trait>();
            PlaceList = new List<Place>();

            

            conn = null;

            //Connect to the database
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            foreach (ConnectionStringSettings c in config.ConnectionStrings.ConnectionStrings)
            {
                if (c.Name == "SqlConnString" && c.ProviderName == "Mysql.Data")
                {
                    conn = new MySqlConnection(c.ConnectionString);
                    try
                    {
                        Console.Write("Connecting to database...");
                        conn.Open();
                        Console.Write("Success!\n");
                        break; //a connection has been opened, no more should be opened
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.ToString());
                    }
                }
            }

            if (conn != null)
            {
                LoadTables();
            }
        }

        ~GenealogyBuilder()
        {
            conn.Close();
        }

        private void editTraitsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            TraitEditor TE = new TraitEditor(TraitList);
            List<Trait> nTraitList = TE.AddTraits();

            /* Insertions */

            StringBuilder cmdString = new StringBuilder("INSERT INTO Traits (`trait_name`, `category`) VALUES ");
            int cCount = 0;
            List<String> parameters = new List<String>();
            foreach (Trait p in nTraitList)
            {
                if (TraitList.Contains(p) == false)
                {
                    if (cCount != 0)
                    {
                        cmdString.Append(", ");
                    }
                    cmdString.Append("(@p" + (cCount++) + ", @p" + (cCount++) + ")");
                    parameters.Add(p.trait_name);
                    parameters.Add(p.category);
                }
            }

            if (cCount != 0)
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = cmdString.ToString();
                cCount = 0;
                foreach (String s in parameters)
                {
                    cmd.Parameters.AddWithValue("p" + (cCount++), s);
                }
                cmd.Prepare();

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    if (System.Diagnostics.Debugger.IsAttached)
                    {
                        Console.WriteLine(ex.ToString());
                        return;
                    }
                }
            }


            /* Deletions */

            StringBuilder delString = new StringBuilder("DELETE FROM Traits WHERE ");
            int dCount = 0;
            parameters = new List<String>();
            foreach (Trait p in TraitList)
            {
                if (nTraitList.Contains(p) == false) //opposite of last section, this finds items which were in the list and no longer are
                {
                    if (dCount != 0)
                    {
                        delString.Append(" OR ");
                    }
                    delString.Append("(`trait_name` = @p" + (dCount++) + " AND `category` = @p" + (dCount++) + ")");
                    parameters.Add(p.trait_name);
                    parameters.Add(p.category);
                }
            }

            if (dCount != 0)
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = delString.ToString();
                dCount = 0;
                foreach (String s in parameters)
                {
                    cmd.Parameters.AddWithValue("p" + (dCount++), s);
                }
                cmd.Prepare();

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    if (System.Diagnostics.Debugger.IsAttached)
                    {
                        Console.WriteLine(ex.ToString());
                        return;
                    }
                }
            }
            

            TraitList = nTraitList;
        }

        private void editPlacesToolStripMenuItem_Click(object sender, EventArgs e)
        {
            PlaceEditor PE = new PlaceEditor(PlaceList);
            List<Place> nPlaceList = PE.AddPlaces();

            /*Insertions*/

            StringBuilder cmdString = new StringBuilder("INSERT INTO Places (`place_id`, `zip_code`, `city`, `state`, `country`) VALUES ");
            int cCount = 0;
            List<String> parameters = new List<String>();
            foreach (Place p in nPlaceList)
            {
                if (PlaceList.Contains(p) == false)
                {
                    if(cCount != 0){
                        cmdString.Append(", ");
                    }
                    cmdString.Append("(@p" + (cCount++) + ", @p" + (cCount++) + ", @p" + (cCount++) + ", @p" + (cCount++) + ", @p" + (cCount++) + ")");
                    parameters.Add(p.place_id.ToString());
                    parameters.Add(p.zip_code);
                    parameters.Add(p.city);
                    parameters.Add(p.state);
                    parameters.Add(p.country);
                }
            }

            if (cCount != 0)
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = cmdString.ToString();
                cCount = 0;
                foreach (String s in parameters)
                {
                    cmd.Parameters.AddWithValue("p" + (cCount++), s);
                }
                cmd.Prepare();

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    if (System.Diagnostics.Debugger.IsAttached)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                }
            }

            /* Deletions */

            StringBuilder delString = new StringBuilder("DELETE FROM Places WHERE `place_id` IN (");
            int dCount = 0;
            foreach (Place p in PlaceList)
            {
                if (nPlaceList.Contains(p) == false) //opposite of last section, this finds items which were in the list and no longer are
                {
                    if (dCount != 0)
                    {
                        delString.Append(", ");
                    }
                    dCount++;
                    delString.Append(p.place_id.ToString()); //no need to prepare it since the value is guaranteed to be an integer
                }
            }

            if (dCount != 0)
            {
                delString.Append(")");
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = delString.ToString();
                cmd.Prepare();
                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    if (System.Diagnostics.Debugger.IsAttached)
                    {
                        Console.WriteLine(ex.ToString());
                        return;
                    }
                }
            }

            PlaceList = nPlaceList;
        }

        private void btnAddPerson_Click(object sender, EventArgs e)
        {
            PersonDialog PD = new PersonDialog(pid,PlaceList);
            if (PD.AddPerson() && PD.newPerson != null)
            {
                MySqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO Person (`person_id`, `name`, `date_of_birth`, `date_of_death`, `from`, `lives_in`) VALUES (@id, @name, @birth, @death, @from, @lives)";
                cmd.Parameters.AddWithValue("id", PD.newPerson.pid);
                cmd.Parameters.AddWithValue("name", PD.newPerson.name);
                cmd.Parameters.AddWithValue("birth", PD.newPerson.birth);

                if(PD.newPerson.death == DateTime.MinValue)
                    cmd.Parameters.AddWithValue("death", null);
                else
                    cmd.Parameters.AddWithValue("death", PD.newPerson.death);
                
                cmd.Parameters.AddWithValue("from", PD.newPerson.from.place_id);
                cmd.Parameters.AddWithValue("lives", PD.newPerson.lives_in.place_id);
                cmd.Prepare();

                try
                {
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    lbPeople.Items.Add(PD.newPerson);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    if (System.Diagnostics.Debugger.IsAttached)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                }
            }
        }

        private void btnRemPerson_Click(object sender, EventArgs e)
        {
            if (lbPeople.SelectedItems.Count != 0)
            {
                List<Object> toDelete = new List<Object>();
                StringBuilder checkMsg = new StringBuilder();
                for (int i = 0; i < lbPeople.SelectedItems.Count; i++)
                {
                    toDelete.Add(lbPeople.SelectedItems[i]);
                    if (i != 0)
                    {
                        if (i + 1 == lbPeople.SelectedItems.Count)
                            checkMsg.Append(" and ");
                        else
                            checkMsg.Append(", ");
                    }

                    checkMsg.Append(((Person) lbPeople.SelectedItems[i]).name);
                }

                DialogResult result;
                result = MessageBox.Show("Are you sure you want to delete " + checkMsg.ToString() + "?", (lbPeople.SelectedItems.Count > 0 ? "Remove People" : "Remove Person"), MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    StringBuilder cmdString = new StringBuilder("DELETE FROM Person WHERE ");
                    bool first = true;
                    foreach (Object item in toDelete)
                    {
                        if (!first)
                        {
                            cmdString.Append(" OR ");
                        }
                        first = false;
                        
                        cmdString.Append("person_id = '" + Convert.ToString(((Person) item).pid) + "'");
                        lbPeople.Items.Remove(item);
                    }
                    MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = cmdString.ToString();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                }
            }
        }

        private void btnAddRelation_Click(object sender, EventArgs e)
        {
            if (lbPeople.SelectedItems.Count == 0 || lbPeople.SelectedItems.Count > 2)
            {
                MessageBox.Show("Select 1 or 2 people to give a relationship.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            else
            {
                RelationDialog RD = new RelationDialog(lbPeople);
                if (lbPeople.SelectedItems.Count == 2)
                {
                    RD.AddPersonA((Person)lbPeople.SelectedItems[0]);
                    RD.AddPersonB((Person)lbPeople.SelectedItems[1]);
                }
                else
                {
                    RD.AddPersonA((Person)lbPeople.SelectedItem);
                }

                if (RD.AddRelation() && RD.newRelation != null)
                {
                    MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO Relatives (`person_a`, `person_b`, `role_a`, `role_b`, `type`, `start_date`, `end_date`) VALUES (@person_a, @person_b, @role_a, @role_b, @type, @start_date, @end_date)";
                    cmd.Parameters.AddWithValue("person_a", RD.newRelation.person_a.pid);
                    cmd.Parameters.AddWithValue("person_b", RD.newRelation.person_b.pid);
                    cmd.Parameters.AddWithValue("role_a", RD.newRelation.role_a);
                    cmd.Parameters.AddWithValue("role_b", RD.newRelation.role_b);
                    cmd.Parameters.AddWithValue("type", RD.newRelation.type);
                    if(RD.newRelation.start_date == DateTime.MinValue)
                        cmd.Parameters.AddWithValue("start_date", null);
                    else
                        cmd.Parameters.AddWithValue("start_date", RD.newRelation.start_date);
                    if (RD.newRelation.end_date == DateTime.MinValue)
                        cmd.Parameters.AddWithValue("end_date", null);
                    else
                        cmd.Parameters.AddWithValue("end_date", RD.newRelation.end_date);

                    cmd.Prepare();

                    try
                    {
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                        RelationList.Add(RD.newRelation);
                        lbRelations.Items.Add(RD.newRelation);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        if (System.Diagnostics.Debugger.IsAttached)
                        {
                            Console.WriteLine(ex.ToString());
                        }
                    }
                }
            }
        }

        private void lbPeople_SelectedIndexChanged(object sender, EventArgs e)
        {
            //List<Person> SelectedPeople = new List<Person>();
            //SelectedPeople = lbPeople.SelectedItems;
            lbRelations.Items.Clear();
            lbTraits.Items.Clear();
            if (lbPeople != null)
            {
                foreach (Person p in lbPeople.SelectedItems)
                {
                    if (RelationList != null)
                    {
                        foreach (Relation r in RelationList)
                        {
                            if ((r.person_a == p || r.person_b == p) && lbRelations.Items.Contains(r) == false)
                            {
                                lbRelations.Items.Add(r);
                            }
                        }
                    }
                    if (HasTraitList != null)
                    {
                        foreach (HasTrait ht in HasTraitList)
                        {
                            if (p == ht.PersonA && lbTraits.Items.Contains(ht) == false)
                            {
                                lbTraits.Items.Add(ht);
                            }
                        }
                    }
                }
            }
        }

        private void selectAllToolStripMenuItem_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < lbPeople.Items.Count; i++)
            {
                lbPeople.SelectedIndex = i;
            }
        }

        private void unselectAllToolStripMenuItem_Click(object sender, EventArgs e)
        {
            lbPeople.SelectedIndex = -1;
        }

        private void btnRemRelation_Click(object sender, EventArgs e)
        {
            if (lbRelations.SelectedItem != null)
            {
                string itmName = lbRelations.SelectedItem.ToString();
                DialogResult result;
                result = MessageBox.Show("Are you sure you want to delete " + itmName + "?", "Remove Relation", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    Relation rmRel = (Relation)lbRelations.SelectedItem;

                    MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE FROM Relatives WHERE `person_a` = @person_a AND `person_b` = @person_b AND `type` = @type";
                    cmd.Parameters.AddWithValue("person_a", rmRel.person_a.pid);
                    cmd.Parameters.AddWithValue("person_b", rmRel.person_b.pid);
                    cmd.Parameters.AddWithValue("type", rmRel.type);

                    cmd.Prepare();

                    try
                    {
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();

                        lbRelations.Items.Remove(lbRelations.SelectedItem);
                        RelationList.Remove(rmRel);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        if (System.Diagnostics.Debugger.IsAttached)
                        {
                            Console.WriteLine(ex.ToString());
                        }
                    }
                }
            }
        }

        private void saveAsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            saveFD.ShowDialog();
            FS = new FileStream(saveFD.FileName, FileMode.Create);
            Save();
            saveToolStripMenuItem.Enabled = true;
        }

        private void Save()
        {
        //    StreamWriter SW = new StreamWriter(FS);
        //    bool first = true;
        //    SW.WriteLine("/*Begin Places*/");
        //    SW.WriteLine("INSERT INTO `Places` (`place_id`, `zip_code`, `city`, `state`, `country`) VALUES");
        //    foreach (Place p in PlaceList)
        //    {
        //        if (first == false)
        //        {
        //            SW.WriteLine(",");
        //        }
        //        string pid = p.place_id.ToString();
        //        string zip = "'" + p.zip_code + "'";
        //        string city = "'" + p.city + "'";
        //        string state = isNULL(p.state);
        //        string country = "'" + p.country + "'";
        //        SW.Write("(" + pid + ", " + zip + ", " + city + ", " + state + ", " + country + ")");
        //        first = false;
        //    }
        //    SW.WriteLine(";");
        //    SW.WriteLine("/*End Places*/");

        //    first = true;
        //    SW.WriteLine("/*Begin People*/");
        //    SW.WriteLine("INSERT INTO `Person` (`person_id`, `name`, `date_of_birth`, `date_of_death`, `from`, `lives_in`) VALUES");
        //    foreach (Person p in lbPeople.Items)
        //    {
        //        if (first == false)
        //        {
        //            SW.WriteLine(",");
        //        }
        //        string pid = p.pid.ToString();
        //        string name = "'" + p.name + "'";
        //        string birth = "'" + p.birth + "'";
        //        string death = isNULL(p.death);
        //        string from_id;
        //        if (p.from == null)
        //        {
        //            from_id = "NULL";
        //        }
        //        else
        //        {
        //            from_id = p.from.place_id.ToString();
        //        }
        //        string lives_id;
        //        if (p.lives_in == null)
        //        {
        //            lives_id = "NULL";
        //        }
        //        else
        //        {
        //            lives_id = p.lives_in.place_id.ToString();
        //        }
        //        SW.Write("(" + pid + ", " + name + ", " + birth + ", " + death + ", " + from_id + ", " + lives_id + ")");
        //        first = false;
        //    }
        //    SW.WriteLine(";");
        //    SW.WriteLine("/*End People*/");

        //    first = true;
        //    SW.WriteLine("/*Begin Relations*/");
        //    SW.WriteLine("INSERT INTO `Relatives` (`person_a`, `person_b`, `role_a`, `role_b`, `type`, `start_date`, `end_date`) VALUES");
        //    foreach (Relation r in RelationList)
        //    {
        //        if (first == false)
        //        {
        //            SW.WriteLine(",");
        //        }
        //        string pidA = r.person_a.pid.ToString();
        //        string pidB = r.person_b.pid.ToString();
        //        string roleA = isNULL(r.role_a);
        //        string roleB = isNULL(r.role_b);
        //        string type = isNULL(r.type);
        //        string start = isNULL(r.start_date);
        //        string end = isNULL(r.end_date);
        //        SW.Write("(" + pidA + ", " + pidB + ", " + roleA + ", " + roleB + ", " + type + ", " + start + ", " + end + ")");
        //        first = false;
        //    }
        //    SW.WriteLine(";");
        //    SW.WriteLine("/*End Relations*/");

        //    first = true;
        //    SW.WriteLine("/*Begin Traits*/");
        //    SW.WriteLine("INSERT INTO `Traits` (`trait_name`, `category`) VALUES");
        //    foreach (Trait t in TraitList)
        //    {
        //        if (first == false)
        //        {
        //            SW.WriteLine(",");
        //        }
        //        string name = isNULL(t.trait_name);
        //        string cat = isNULL(t.category);
        //        SW.Write("(" + name + ", " + cat + ")");
        //        first = false;
        //    }
        //    SW.WriteLine(";");
        //    SW.WriteLine("/*End Traits*/");

        //    first = true;
        //    SW.WriteLine("/*Begin HasTraits*/");
        //    SW.WriteLine("INSERT INTO `HasTraits` (`person`, `trait`) VALUES");
        //    foreach (HasTrait ht in HasTraitList)
        //    {
        //        if (first == false)
        //        {
        //            SW.WriteLine(",");
        //        }
        //        string id  = ht.PersonA.pid.ToString();
        //        string traitname = isNULL(ht.the_trait.trait_name);
        //        SW.Write("(" + id + ", " + traitname + ")");
        //        first = false;
        //    }
        //    SW.WriteLine(";");
        //    SW.WriteLine("/*End HasTraits*/");

        //    SW.Close();
        }

        public string isNULL(string str)
        {
            if (str == "")
            {
                return "NULL";
            }
            return "'"+str+"'";
        }

        private void btnRemTrait_Click(object sender, EventArgs e)
        {
            if (lbTraits.SelectedItem != null)
            {
                string itmName = lbTraits.SelectedItem.ToString();
                DialogResult result;
                result = MessageBox.Show("Are you sure you want to delete " + itmName + "?", "Remove Trait", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    HasTrait rmTrait = (HasTrait)lbTraits.SelectedItem;
                    MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "DELETE FROM HasTraits WHERE `trait` = @trait_name AND `person` = @person_id";
                    cmd.Parameters.AddWithValue("trait_name", rmTrait.the_trait.trait_name);
                    cmd.Parameters.AddWithValue("person_id", rmTrait.PersonA.pid);
                    cmd.Prepare();
                    try
                    {
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        HasTraitList.Remove(rmTrait);
                        lbTraits.Items.Remove(lbTraits.SelectedItem);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        if (System.Diagnostics.Debugger.IsAttached)
                        {
                            Console.WriteLine(ex.ToString());
                        }
                    }
                }
            }
        }

        private void btnAddTrait_Click(object sender, EventArgs e)
        {
            if (lbPeople.SelectedItem != null)
            {
                HasTraitDialog HTD = new HasTraitDialog(TraitList);
                if (HTD.GetTrait())
                {
                    HasTrait PersonTrait = new HasTrait((Person)lbPeople.SelectedItem, HTD.SelectedTrait);

                    MySqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO HasTraits (`person`, `trait`) VALUES (@person_id, @trait_name)";
                    cmd.Parameters.AddWithValue("person_id", PersonTrait.PersonA.pid);
                    cmd.Parameters.AddWithValue("trait_name", PersonTrait.the_trait.trait_name);
                    cmd.Prepare();

                    try
                    {
                        cmd.ExecuteNonQuery();
                        cmd.Dispose();
                        HasTraitList.Add(PersonTrait);
                        lbTraits.Items.Add(PersonTrait);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        if (System.Diagnostics.Debugger.IsAttached)
                        {
                            Console.WriteLine(ex.ToString());
                        }
                    }
                }
            }
        }

        private void saveToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Save();
        }

        private void openToolStripMenuItem_Click(object sender, EventArgs e)
        {
            openFD.ShowDialog();
            FS = new FileStream(openFD.FileName, FileMode.OpenOrCreate);
            saveToolStripMenuItem.Enabled = true;
            LoadFile();
        }

        private void LoadTables()
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.Connection = conn;

            try{
                cmd.CommandText = "SELECT `place_id`, `zip_code`, `city`, `state`, `country` FROM Places";
                MySqlDataReader reader = cmd.ExecuteReader();
                int largest = 0;
                while (reader.Read())
                {
                    int id = reader.GetInt32(0);

                    if (id > largest)
                    {
                        largest = id;
                    }

                    PlaceList.Add(new Place(id, reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4)));
                }
                Place.ID = ++largest;
                
                reader.Close();
                cmd.CommandText = "SELECT `trait_name`, `category` FROM Traits";
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    TraitList.Add(new Trait(reader.GetString(0), reader.GetString(1)));
                }
                reader.Close();
                
                
                cmd.CommandText = "SELECT `person_id`, `name`, `date_of_birth`, `date_of_death`, `from`, `lives_in`  FROM Person";
                reader = cmd.ExecuteReader();
                
                largest = 0;
                while (reader.Read())
                {
                    Place A = null;
                    Place B = null;
                    Console.WriteLine("Person is " + reader.GetString(1));
                    Object from_id = reader.GetValue(4);
                    if (!(from_id is DBNull))
                    {
                        foreach (Place p in PlaceList)
                        {
                            if (p.place_id == Convert.ToInt32(from_id))
                            {
                                A = p;
                            }
                        }
                    }

                    Object lives_in_id = reader.GetValue(5);
                    if (!(lives_in_id is DBNull))
                    {
                        foreach (Place p in PlaceList)
                        {
                            if (p.place_id == Convert.ToInt32(lives_in_id))
                            {
                                B = p;
                            }
                        }
                    }

                    int pid = reader.GetInt32(0);
                    if (pid > largest) largest = pid;

                    Object getBirthday = reader.GetValue(2);
                    DateTime birthday = ((getBirthday is DBNull) ? DateTime.MinValue : reader.GetDateTime(2));

                    Object getDeathday = reader.GetValue(3);
                    DateTime deathday = ((getDeathday is DBNull) ? DateTime.MinValue : reader.GetDateTime(3));

                    lbPeople.Items.Add(new Person(pid, reader.GetString(1), B, A, birthday, deathday));
                }
                Person.ID = ++largest;

                reader.Close();
                cmd.CommandText = "SELECT `person`, `trait` FROM HasTraits";
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Person A = null;
                    Trait B = null;

                    int pid = reader.GetInt32(0);
                    string tname = reader.GetString(1);

                    foreach (Person p in lbPeople.Items)
                    {
                        if (p.pid == pid)
                        {
                            A = p;
                        }
                    }
                    foreach (Trait t in TraitList)
                    {
                        if (t.trait_name.Equals(tname))
                        {
                            B = t;
                        }
                    }
                    HasTraitList.Add(new HasTrait(A, B));
                }
                
                reader.Close();
                cmd.CommandText = "SELECT `person_a`, `person_b`, `role_a`, `role_b`, `type`, `start_date`, `end_date` FROM Relatives";
                reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    Person A = null;
                    Person B = null;

                    int pid_a = reader.GetInt32(0);
                    int pid_b = reader.GetInt32(1);

                    foreach (Person p in lbPeople.Items)
                    {
                        if (p.pid == pid_a)
                        {
                            A = p;
                        }
                        if (p.pid == pid_b)
                        {
                            B = p;
                        }
                    }

                    Object get_start_date = reader.GetValue(5);
                    DateTime start_date = (get_start_date is DBNull) ? DateTime.MinValue : reader.GetDateTime(5);
                    Object get_end_date = reader.GetValue(6);
                    DateTime end_date = (get_end_date is DBNull) ? DateTime.MinValue : reader.GetDateTime(6);

                    RelationList.Add(new Relation(A, B, reader.GetString(2), reader.GetString(3), reader.GetString(4), start_date, end_date));
                }

                reader.Close();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                try
                {
                    Console.WriteLine("Tables not found. Creating.");
                    //DB setup boilerplate
                    cmd = conn.CreateCommand();
                    cmd.CommandText = Query.GetCreator();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    Console.WriteLine("Tables created.");
                }
                catch (Exception ex2)
                {
                    Console.WriteLine(ex2);
                }
            }
        }

        private void LoadFile()
        {
            /*List<string> People;
            List<string> Places;
            List<string> Relations;
            List<string> Traits;
            List<string> HasTraits;

            StreamReader SR = new StreamReader(FS);
            SR.ReadLine();
            Places = LoadList(SR);
            SR.ReadLine();
            SR.ReadLine();
            People = LoadList(SR);
            SR.ReadLine();
            SR.ReadLine();
            Relations = LoadList(SR);
            SR.ReadLine();
            SR.ReadLine();
            Traits = LoadList(SR);
            SR.ReadLine();
            SR.ReadLine();
            HasTraits = LoadList(SR);
            SR.Close();
            //Load Places
            int largest = 0;
            foreach (string s in Places)
            {
                string var = s;
                string[] place = new string[5];
                char[] toTrim = {'(',')',';',','};
                var = var.Trim(toTrim);
                //var = var.TrimStart('(');
                //var = var.TrimEnd(';');
                //var = var.TrimEnd(',');
                //var = var.TrimEnd(')');
                place = var.Split(',');
                for (int i = 0; i < place.Length; i++)
                {
                    place[i] = place[i].Trim();
                    place[i] = place[i].Trim('\'');
                }
                if (place[3].Equals("NULL")) place[3] = "";
                if (Convert.ToInt32(place[0]) > largest) largest = Convert.ToInt32(place[0]);
                PlaceList.Add(new Place(Convert.ToInt32(place[0]), place[1],place[2],place[3],place[4]));
            }
            Place.ID = ++largest;
            //Load Traits
            foreach (string s in Traits)
            {
                string var = s;
                string[] trait = new string[2];
                char[] toTrim = { '(', ')', ';', ',' };
                var = var.Trim(toTrim);
                trait = var.Split(',');
                for (int i = 0; i < trait.Length; i++)
                {
                    trait[i] = trait[i].Trim();
                    trait[i] = trait[i].Trim('\'');
                }
                TraitList.Add(new Trait(trait[0],trait[1]));
            }

            //Load People
            largest = 0;
            foreach (string s in People)
            {
                string var = s;
                string[] person = new string[6];
                char[] toTrim = { '(', ')', ';', ',' };
                var = var.Trim(toTrim);
                person = var.Split(',');
                for (int i = 0; i < person.Length; i++)
                {
                    person[i] = person[i].Trim();
                    person[i] = person[i].Trim('\'');
                }
                Place A = null;
                Place B = null;
                if (person[3].Equals("NULL")) person[3] = "";
                if (!person[4].Equals("NULL"))
                {
                    foreach (Place p in PlaceList)
                    {
                        if (p.place_id == Convert.ToInt32(person[4]))
                        {
                            A = p;
                        }
                    }
                }
                if (!person[5].Equals("NULL"))
                {
                    foreach (Place p in PlaceList)
                    {
                        if (p.place_id == Convert.ToInt32(person[5]))
                        {
                            B = p;
                        }
                    }
                }
                if (Convert.ToInt32(person[0]) > largest) largest = Convert.ToInt32(person[0]);
                lbPeople.Items.Add(new Person(Convert.ToInt32(person[0]),person[1],B,A,person[2],person[3]));
            }
            Person.ID = ++largest;

            //Load HasTraits
            foreach (string s in HasTraits)
            {
                string var = s;
                string[] has = new string[2];
                char[] toTrim = { '(', ')', ';', ',' };
                var = var.Trim(toTrim);
                has = var.Split(',');
                for (int i = 0; i < has.Length; i++)
                {
                    has[i] = has[i].Trim();
                    has[i] = has[i].Trim('\'');
                }
                Person A = null;
                Trait B = null;
                foreach (Person p in lbPeople.Items)
                {
                    if (p.pid == Convert.ToInt32(has[0]))
                    {
                        A = p;
                    }
                }
                foreach (Trait t in TraitList)
                {
                    if (t.trait_name.Equals(has[1]))
                    {
                        B = t;
                    }
                }
                HasTraitList.Add(new HasTrait(A,B));
            }

            //load relationships
            foreach (string s in Relations)
            {
                string var = s;
                string[] rel = new string[7];
                char[] toTrim = { '(', ')', ';', ',' };
                var = var.Trim(toTrim);
                rel = var.Split(',');
                for (int i = 0; i < rel.Length; i++)
                {
                    rel[i] = rel[i].Trim();
                    rel[i] = rel[i].Trim('\'');
                }
                Person A = null;
                Person B = null;
                foreach (Person p in lbPeople.Items)
                {
                    if (p.pid == Convert.ToInt32(rel[0]))
                    {
                        A = p;
                    }
                    if (p.pid == Convert.ToInt32(rel[1]))
                    {
                        B = p;
                    }
                }
                if (rel[5].Equals("NULL")) rel[5] = "";
                if (rel[6].Equals("NULL")) rel[6] = "";
                RelationList.Add(new Relation(A,B,rel[2],rel[3],rel[4],rel[5],rel[6]));
            }*/
        }

        private List<string> LoadList(StreamReader SR)
        {
            string inputString = SR.ReadLine();
            List<string> LS = new List<string>();
            while (!inputString.Contains(';'))
            {
                inputString = SR.ReadLine();
                LS.Add(inputString);
            }
            return LS;

        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            if (lbPeople.Items.Count == 0)
                MessageBox.Show("Select at least 1 person.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            else
            {
                SelectDialog SB = new SelectDialog(TraitList, PlaceList);

                SB.cmd = conn.CreateCommand();
                
                SB.ShowDialog();

                if (SB.cmd != null && SB.cmd.CommandText.Length > 0)
                { //if cmd is null, selection was cancelled
                    Console.WriteLine(SB.cmd.CommandText);
                    List<int> found = new List<int>();

                    MySqlDataReader reader = SB.cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        found.Add(reader.GetInt32(0));
                    }
                    Console.WriteLine("Found " + found.Count + " people");
                    reader.Close();
                    SB.cmd.Dispose();

                    bool fromCurrent = SB.ShouldSelectFromCurrent();

                    if (found.Count > 0)
                    {
                        for (int index = 0; index < lbPeople.Items.Count; index++)
                        {
                            bool wasSelected = lbPeople.GetSelected(index);

                            lbPeople.SetSelected(index, false);
                            
                            foreach (int id in found)
                            {
                                if (id == ((Person)lbPeople.Items[index]).pid && (!fromCurrent || wasSelected == true))
                                    lbPeople.SetSelected(index, true);
                            }
                        }
                    }
                }
            }
        }

        private void btnMakeTree_Click(object sender, EventArgs e)
        {
            if (lbPeople.Items.Count < 2)
                MessageBox.Show("Select at least 2 people.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            else
            {
                TreeViewer TR = new TreeViewer();
                List<Person> PeopleList = new List<Person>();
                if (lbPeople.SelectedItems.Count != 0)
                {
                    for (int index = 0; index < lbPeople.SelectedItems.Count; index++)
                    {
                        PeopleList.Add((Person)lbPeople.SelectedItems[index]);
                    }
                }
                else
                {
                    for (int index = 0; index < lbPeople.Items.Count; index++)
                    {
                        PeopleList.Add((Person)lbPeople.Items[index]);
                    }
                }
                
                TR.Make(PeopleList, RelationList);
            }
        }

        private void lbRelations_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void toolStripSeparator4_Click(object sender, EventArgs e)
        {

        }

        private void connectToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Connect cd = new Connect();
            
            MySqlConnection nConn = null;

            while (nConn == null)
            {
                try
                {
                    cd.PrepConnectionString();
                    cd.ShowDialog();
                    string connString = cd.GetConnectionString();
                    
                    if (connString == null)
                    {
                        break;
                    }
                    
                    nConn = new MySqlConnection(connString);

                    Console.Write("Connecting to database...");
                    nConn.Open();
                    Console.Write("Success!\n");

                    if (conn != null)
                    {
                        conn.Close();
                    }

                    conn = nConn;
                    LoadTables();
                    MessageBox.Show("Connection established.", "Success!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Could not connect, please verify your credentials.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    nConn = null;
                }
            }

        }

        private void GenealogyBuilder_Load(object sender, EventArgs e)
        {

        }

        private void addPersonToolStripMenuItem_Click(object sender, EventArgs e)
        {
            btnAddPerson_Click(sender, e);
        }

        private void selectToolStripMenuItem_Click(object sender, EventArgs e)
        {
            btnSelect_Click(sender, e);
        }

        private void makeTreeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            btnMakeTree_Click(sender, e);
        }

        private void traceLineageToolStripMenuItem_Click(object sender, EventArgs e)
        {
            List<Person> People = new List<Person>();
            foreach (Object p in lbPeople.Items)
            {
                People.Add((Person)p);
            }
            TraceLineageDialog trace = new TraceLineageDialog(People);
            trace.ShowDialog();

            if (trace.root != null)
            {
                MySqlCommand cmd = conn.CreateCommand();
                List<int> toSelect = new List<int>();
                if (trace.genBack != 0)
                {
                    cmd.CommandText = Query.BackwardGenerations(trace.genBack, trace.root.pid);
                    Console.WriteLine(cmd.CommandText);
                    MySqlDataReader reader = cmd.ExecuteReader();
                    
                    while (reader.Read())
                    {
                        toSelect.Add(reader.GetInt32(0));
                    }
                    reader.Close();
                    cmd.Dispose();
                }
                if (trace.genForward != 0)
                {
                    cmd.CommandText = Query.GenerationsForwardViews(trace.genForward, trace.root.pid);
                    Console.WriteLine(cmd.CommandText);
                    MySqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        toSelect.Add(reader.GetInt32(0));
                    }
                    reader.Close();
                    cmd.Dispose();
                }

                for (int index = 0; index < lbPeople.Items.Count; index++)
                {
                    lbPeople.SetSelected(index, toSelect.Contains(((Person)lbPeople.Items[index]).pid));
                }
            }
        }

        private void btnEditPerson_Click(object sender, EventArgs e)
        {
            PersonDialog PD = new PersonDialog((Person)lbPeople.SelectedItem, PlaceList);
            PD.Text = "Edit Person";
            PD.ShowDialog();

            MySqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "UPDATE Person SET `name` = @name, `date_of_birth` = @birth, `date_of_death` = @death, `from` = @from, `lives_in` = @lives WHERE person_id = @pid";
            cmd.Parameters.AddWithValue("pid", PD.newPerson.pid);
            cmd.Parameters.AddWithValue("name", PD.newPerson.name);
            cmd.Parameters.AddWithValue("birth", PD.newPerson.birth);

            if (PD.newPerson.death == DateTime.MinValue)
                cmd.Parameters.AddWithValue("death", null);
            else
                cmd.Parameters.AddWithValue("death", PD.newPerson.death);

            if(PD.newPerson.from != null)
                cmd.Parameters.AddWithValue("from", PD.newPerson.from.place_id);
            else
                cmd.Parameters.AddWithValue("from", null);
            if(PD.newPerson.lives_in != null)
                cmd.Parameters.AddWithValue("lives", PD.newPerson.lives_in.place_id);
            else
                cmd.Parameters.AddWithValue("lives", null);
            cmd.Prepare();

            try
            {
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                lbPeople.Items[lbPeople.SelectedIndex] = PD.newPerson;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Could not commit data.", "Error!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                if (System.Diagnostics.Debugger.IsAttached)
                {
                    Console.WriteLine(ex.ToString());
                }
            }
        }

        private void btnTraceLineage_Click(object sender, EventArgs e)
        {
            traceLineageToolStripMenuItem_Click(sender, e);
        }
    }
}
